#!/bin/bash
#SQOOP_HOME=/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin/sqoop
SQOOP_HOME=/usr/bin/sqoop
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

echo '========================================'
echo '==============开始增量导入==============='
echo '========================================'

# ========== 全量 =======================

# 1、customer_relationship客户意向表---------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *,'${TD_DATE}' as dt from customer_relationship where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table customer_relationship \
--split-by id \
--m 100
wait

# 2、customer clue客户线索表-----------------------------------------------
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query 'select *,DATE_SUB(curdate(),INTERVAL 1 DAY) as dt from customer_clue where $CONDITIONS' \
--hcatalog-database notp_ods \
--hcatalog-table customer_clue \
--split-by id \
--m 100

# 3、Customer客户静态信息表----------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from customer where 1=1 and \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table customer \
--split-by id \
--m 100

# 4、employee员工表----------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *,'${TD_DATE}' as dt from employee where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table employee \
--split-by id \
--m 100
wait

# 5、scrm_department部门表----------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from scrm_department where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table scrm_department \
--split-by id \
--m 100
wait

# 6、itcast_school学校表----------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *,'${TD_DATE}' as dt from itcast_school where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table itcast_school \
--split-by id \
--m 100
wait

# 7、itcast_subject学科表----------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from itcast_subject where 1=1 and  \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table itcast_subject \
--split-by id \
--m 100

# 4. 报名课程表----------------------------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/scrm \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from itcast_clazz where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table itcast_clazz \
-m 100 \
--split-by id
wait


# EMS-PV测试表-----------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/nev \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select id, create_date_time, session_id, sid, create_time, seo_source, seo_keywords, ip, area, country, province, city, origin_channel, user as user_match, manual_time, begin_time, end_time, last_customer_msg_time_stamp, last_agent_msg_time_stamp, reply_msg_count, msg_count, browser_name, os_info, '${TD_DATE}' as dt from web_chat_ems_2019_07 where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table web_chat_ems_2019_07 \
-m 100 \
--split-by id
wait


# 访问会话信息表------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/nev \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select id,referrer,from_url,landing_page_url,url_title,platform_description,other_params,history, '${TD_DATE}' as dt from web_chat_text_ems_2019_07 where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table web_chat_text_ems_2019_07 \
-m 100 \
--split-by id
wait

# 1. 班级在读人数表------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select id, school_id, subject_id, class_id, ifnull(studying_student_count,0) studying_student_count, studying_date, '${TD_DATE}' as dt from class_studying_student_count where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table class_studying_student_count \
-m 100 \
--split-by id
wait

# 2. 班级课表------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from course_table_upload_detail where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table course_table_upload_detail \
-m 100 \
--split-by id
wait

# 3. 班级作息时间表---------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from tbh_class_time_table where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table tbh_class_time_table \
-m 100 \
--split-by id
wait

# 4. 学生打卡记录表---------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from tbh_student_signin_record where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table tbh_student_signin_record \
-m 100 \
--split-by id
wait

# 5. 学生请假申请表----------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from student_leave_apply where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table student_leave_apply \
-m 100 \
--split-by id
wait

# '线索投诉表'-----------------------------------------------------------------------------------
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query 'select `id`,`customer_relationship_first_id`,`employee_id`,`employee_name`,`employee_department_id`,`employee_tdepart_id`,`appeal_status`,`audit_id`,`audit_name`,`audit_department_id`,`audit_department_name`,`audit_date_time`,`create_date_time`,`update_date_time`,`deleted`,`tenant`,DATE_SUB(curdate(),INTERVAL 1 DAY) as dt from customer_appeal where $CONDITIONS' \
--hcatalog-database notp_ods \
--hcatalog-table customer_appeal \
--split-by id \
--m 100

#  往 日历 表导入数据-----------------------------------------------------------------------------
/usr/bin/sqoop import \
--connect jdbc:mysql://106.75.33.59:3306/teach \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${TD_DATE}' as dt from calendar where \$CONDITIONS" \
--hcatalog-database notp_ods \
--hcatalog-table calendar \
-m 100 \
--split-by id

echo '========================================'
echo '================success================='
echo '========================================'
